import tkinter as tk
from tkinter import ttk, messagebox
import sqlite3
import sys


class DatabaseApp:
    def __init__(self, root):
        self.root = root
        self.root.title("数据库管理系统")
        self.root.geometry("800x600")

        # 创建数据库连接
        try:
            self.conn = sqlite3.connect("example.db")
            self.cursor = self.conn.cursor()
            # 创建示例表（如果不存在）
            self.create_table()
        except Exception as e:
            messagebox.showerror("数据库连接错误", f"无法连接到数据库: {str(e)}")
            sys.exit(1)

        # 创建GUI组件
        self.create_widgets()

        # 初始化数据显示
        self.refresh_data()

    def create_table(self):
        # 创建一个示例表
        self.cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE,
            age INTEGER
        )
        ''')
        self.conn.commit()

    def create_widgets(self):
        # 创建顶部菜单
        self.create_menu()

        # 创建主框架
        main_frame = ttk.Frame(self.root, padding="10")
        main_frame.pack(fill=tk.BOTH, expand=True)

        # 创建数据显示区域
        self.create_display_area(main_frame)

        # 创建操作按钮区域
        self.create_action_buttons(main_frame)

        # 创建输入表单区域
        self.create_input_form(main_frame)

    def create_menu(self):
        menu_bar = tk.Menu(self.root)

        # 文件菜单
        file_menu = tk.Menu(menu_bar, tearoff=0)
        file_menu.add_command(label="退出", command=self.root.quit)
        menu_bar.add_cascade(label="文件", menu=file_menu)

        # 设置菜单
        settings_menu = tk.Menu(menu_bar, tearoff=0)
        settings_menu.add_command(label="关于", command=self.show_about)
        menu_bar.add_cascade(label="设置", menu=settings_menu)

        self.root.config(menu=menu_bar)

    def show_about(self):
        messagebox.showinfo("关于", "数据库管理系统 v1.0\n用于演示数据库的增删改查操作")

    def create_display_area(self, parent):
        display_frame = ttk.LabelFrame(parent, text="数据列表", padding="10")
        display_frame.pack(fill=tk.BOTH, expand=True, pady=10)

        # 创建Treeview组件用于显示数据
        columns = ("id", "name", "email", "age")
        self.tree = ttk.Treeview(display_frame, columns=columns, show="headings")

        # 设置列标题
        self.tree.heading("id", text="ID")
        self.tree.heading("name", text="姓名")
        self.tree.heading("email", text="邮箱")
        self.tree.heading("age", text="年龄")

        # 设置列宽
        self.tree.column("id", width=50)
        self.tree.column("name", width=150)
        self.tree.column("email", width=200)
        self.tree.column("age", width=80)

        self.tree.pack(fill=tk.BOTH, expand=True)

        # 添加垂直滚动条
        scrollbar = ttk.Scrollbar(display_frame, orient=tk.VERTICAL, command=self.tree.yview)
        self.tree.configure(yscroll=scrollbar.set)
        scrollbar.pack(side=tk.RIGHT, fill=tk.Y)

        # 绑定选择事件
        self.tree.bind("<<TreeviewSelect>>", self.on_select)

    def create_action_buttons(self, parent):
        button_frame = ttk.Frame(parent, padding="10")
        button_frame.pack(fill=tk.X, pady=10)

        self.add_button = ttk.Button(button_frame, text="添加", command=self.add_data)
        self.add_button.pack(side=tk.LEFT, padx=5)

        self.update_button = ttk.Button(button_frame, text="更新", command=self.update_data, state=tk.DISABLED)
        self.update_button.pack(side=tk.LEFT, padx=5)

        self.delete_button = ttk.Button(button_frame, text="删除", command=self.delete_data, state=tk.DISABLED)
        self.delete_button.pack(side=tk.LEFT, padx=5)

        self.refresh_button = ttk.Button(button_frame, text="刷新", command=self.refresh_data)
        self.refresh_button.pack(side=tk.LEFT, padx=5)

    def create_input_form(self, parent):
        form_frame = ttk.LabelFrame(parent, text="数据操作", padding="10")
        form_frame.pack(fill=tk.X, pady=10)

        # 创建输入字段
        ttk.Label(form_frame, text="ID:").grid(row=0, column=0, sticky=tk.W, pady=5)
        self.id_var = tk.StringVar()
        ttk.Entry(form_frame, textvariable=self.id_var, state="readonly", width=10).grid(row=0, column=1, sticky=tk.W,
                                                                                         pady=5)

        ttk.Label(form_frame, text="姓名:").grid(row=0, column=2, sticky=tk.W, pady=5)
        self.name_var = tk.StringVar()
        ttk.Entry(form_frame, textvariable=self.name_var, width=20).grid(row=0, column=3, sticky=tk.W, pady=5)

        ttk.Label(form_frame, text="邮箱:").grid(row=1, column=0, sticky=tk.W, pady=5)
        self.email_var = tk.StringVar()
        ttk.Entry(form_frame, textvariable=self.email_var, width=20).grid(row=1, column=1, sticky=tk.W, pady=5)

        ttk.Label(form_frame, text="年龄:").grid(row=1, column=2, sticky=tk.W, pady=5)
        self.age_var = tk.StringVar()
        ttk.Entry(form_frame, textvariable=self.age_var, width=10).grid(row=1, column=3, sticky=tk.W, pady=5)

    def on_select(self, event):
        # 处理Treeview选择事件
        selection = self.tree.selection()
        if selection:
            item = self.tree.item(selection[0])
            values = item["values"]
            self.id_var.set(values[0])
            self.name_var.set(values[1])
            self.email_var.set(values[2])
            self.age_var.set(values[3])

            # 启用更新和删除按钮
            self.update_button.config(state=tk.NORMAL)
            self.delete_button.config(state=tk.NORMAL)
        else:
            # 没有选择项时禁用按钮
            self.update_button.config(state=tk.DISABLED)
            self.delete_button.config(state=tk.DISABLED)

    def refresh_data(self):
        # 清空Treeview
        for item in self.tree.get_children():
            self.tree.delete(item)

        # 从数据库加载数据
        try:
            self.cursor.execute("SELECT * FROM users")
            rows = self.cursor.fetchall()

            for row in rows:
                self.tree.insert("", tk.END, values=row)

            # 清空输入字段
            self.clear_form()

            # 禁用更新和删除按钮
            self.update_button.config(state=tk.DISABLED)
            self.delete_button.config(state=tk.DISABLED)
        except Exception as e:
            messagebox.showerror("错误", f"加载数据失败: {str(e)}")

    def clear_form(self):
        self.id_var.set("")
        self.name_var.set("")
        self.email_var.set("")
        self.age_var.set("")

    def add_data(self):
        name = self.name_var.get()
        email = self.email_var.get()
        age = self.age_var.get()

        if not name or not email:
            messagebox.showerror("错误", "姓名和邮箱不能为空")
            return

        try:
            age_value = int(age) if age else None
            self.cursor.execute("INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
                                (name, email, age_value))
            self.conn.commit()
            messagebox.showinfo("成功", "数据添加成功")
            self.refresh_data()
        except sqlite3.IntegrityError:
            messagebox.showerror("错误", "邮箱地址已存在")
        except Exception as e:
            messagebox.showerror("错误", f"添加数据失败: {str(e)}")

    def update_data(self):
        id_value = self.id_var.get()
        name = self.name_var.get()
        email = self.email_var.get()
        age = self.age_var.get()

        if not id_value or not name or not email:
            messagebox.showerror("错误", "ID、姓名和邮箱不能为空")
            return

        try:
            age_value = int(age) if age else None
            self.cursor.execute("UPDATE users SET name=?, email=?, age=? WHERE id=?",
                                (name, email, age_value, id_value))
            self.conn.commit()
            messagebox.showinfo("成功", "数据更新成功")
            self.refresh_data()
        except sqlite3.IntegrityError:
            messagebox.showerror("错误", "邮箱地址已存在")
        except Exception as e:
            messagebox.showerror("错误", f"更新数据失败: {str(e)}")

    def delete_data(self):
        id_value = self.id_var.get()

        if not id_value:
            messagebox.showerror("错误", "请选择要删除的数据")
            return

        if messagebox.askyesno("确认", "确定要删除这条数据吗?"):
            try:
                self.cursor.execute("DELETE FROM users WHERE id=?", (id_value,))
                self.conn.commit()
                messagebox.showinfo("成功", "数据删除成功")
                self.refresh_data()
            except Exception as e:
                messagebox.showerror("错误", f"删除数据失败: {str(e)}")

    def __del__(self):
        # 关闭数据库连接
        if hasattr(self, 'conn'):
            self.conn.close()


def main():
    root = tk.Tk()
    app = DatabaseApp(root)
    root.mainloop()


if __name__ == "__main__":
    main()    